﻿using CuaHangLego.Bussiness;
using CuaHangLego.Bussiness.Parser;
using CuaHangLego.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CuaHangLego.DataAccess
{
    public class NhatKySanPhamDA : DABase
    {
        public static NhatKySanPhamDA Instance = new NhatKySanPhamDA();
        public bool Insert(int MaSP)
        {
            int SoLuongHienTai = GetQuatity(MaSP);
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("INSERT INTO NHATKYSP ({0},{1},{2}) VALUES({3},{4},'{5}');",
                   "SanPhamID", "SoLuong", "ThoiGian",
                   MaSP, SoLuongHienTai, TConvert.CSDateTimeToSQL(DateTime.Parse(DateTime.Now.ToShortDateString())));
            this.OpenDB();
            int rs = ExecuteQuery(sql);
            this.CloseDB();
            if (rs == 0)
            {
                return Update(MaSP);
            }
            return rs > 0;
        }

        private int GetQuatity(int MaSP)
        {
            int n = 0;
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("SELECT SoLuong FROM SANPHAM WHERE {0}={1};", "ID", MaSP);
            this.OpenDB();
            SqlDataReader reader = ExecuteQueryReader(sql);
            while (reader.Read())
            {
                n = int.Parse(reader["SoLuong"].ToString());
                break;
            }
            this.CloseDB();
            return n;
        }

        public bool Update(int MaSP)
        {
            int SoLuongHienTai = GetQuatity(MaSP);
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE NHATKYSP SET {0}={1} WHERE {2}={3} AND {4}>='{5}'",
                "SoLuong", SoLuongHienTai,
                "SanPhamID", MaSP,
                "ThoiGian", TConvert.CSDateTimeToSQL(DateTime.Parse(DateTime.Now.ToShortDateString())));
            this.OpenDB();
            int rs = ExecuteQuery(sql);
            this.CloseDB();
            return rs > 0;
        }

        public bool DeleteFromHD(int hd, NhatKySanPham item)
        {
            throw new NotImplementedException();
        }

        public IList<NhatKySanPham> GetAllOfSanPham(int MaSP, DateTime ThoiGian1, DateTime ThoiGian2)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM NHATKYSP WHERE {0}={1} AND ThoiGian >='{2}' AND ThoiGian < '{3}'",
                "SanPhamID", MaSP,
                TConvert.CSDateTimeToSQL(DateTime.Parse(ThoiGian1.ToShortDateString())),
                TConvert.CSDateTimeToSQL(DateTime.Parse(ThoiGian2.AddDays(1).ToShortDateString())));
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<NhatKySanPham> list = new List<NhatKySanPham>();
            while (reader.Read())
            {
                list.Add(NhatKySanPhamParser.Parse(reader));
            }
            CloseDB();
            return list;
        }

        public IList<NhatKySanPham> GetAll()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM NHATKYSP");
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<NhatKySanPham> list = new List<NhatKySanPham>();
            while (reader.Read())
            {
                list.Add(NhatKySanPhamParser.Parse(reader));
            }
            CloseDB();
            return list;
        }
    }
}
